package com.example.demo.mapper;

import com.example.demo.entity.Image;
import com.example.demo.utils.DBUtil;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URL;
import java.net.URLConnection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class SelectImage {

//    图片查询
    public List<Image> query() throws Exception{
        Connection con = DBUtil.getConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("select * from Image;");
        List<Image> ImageList = new ArrayList<Image>();
        Image Image = null;

        while (rs.next()){
            Image = new Image();

            Image.setImagehttps(rs.getString("Imagehttps"));
            Image.setType(rs.getInt("type_id"));
            Image.setFormerImagehttp(rs.getString("FormerImagehttp"));
            Image.setTitle(rs.getString("title"));
            Image.setId(rs.getLong("ImageId"));

            ImageList.add(Image);
        }
        return ImageList;
    }

//    图片插入

    public void addImage(Image Image) throws Exception{

        Connection con = DBUtil.getConnection();
        String sql="insert into Image"
                + "(imagehttps,title,type_id,FormerImagehttp)"
                + "values("
                + "?,?,?,?)";
        PreparedStatement psmt = con.prepareStatement(sql);
        psmt.setString(1, Image.getImagehttps());
        psmt.setString(2, Image.getTitle());
        psmt.setLong(3,Image.getType());
        psmt.setString(4,Image.getFormerImagehttp());
        psmt.execute();
    }

//模糊查询
    public List<Image> vagueImageSelect(String title) throws  Exception{

        Connection con = DBUtil.getConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("select * from Image where title like '%"+title+"%'");
        List<Image> ImageList = new ArrayList<Image>();
        Image Image = null;

        while (rs.next()){
            Image = new Image();

            Image.setImagehttps(rs.getString("Imagehttps"));
            Image.setType(rs.getInt("type_id"));
            Image.setFormerImagehttp(rs.getString("FormerImagehttp"));
            Image.setTitle(rs.getString("title"));
            Image.setId(rs.getLong("ImageId"));

            ImageList.add(Image);
        }
        return ImageList;
    }

//    图片删除

    public void deleteImage(Image image) throws  Exception{
        Connection con=DBUtil.getConnection();
        String sql="" +
                "DELETE FROM Image "+
                "WHERE imagehttps = ? and FormerImagehttp = ?";
        PreparedStatement psmt=con.prepareStatement(sql);
        psmt.setString(1,image.getImagehttps());
        psmt.setString(2,image.getFormerImagehttp());
        psmt.execute();
    }


//    图片修改
    public void updata(Image oldImage,Image newImage)throws Exception{
        Connection con=DBUtil.getConnection();
        String sql="update Image" +
                "set imagehttps = ? ,title = ?,type_id = ?,FormerImagehttp = ?" +
                "where imagehttps = ? and FormerImagehttp =?";
        PreparedStatement psmt=con.prepareStatement(sql);
        psmt.setString(1,newImage.getImagehttps());
        psmt.setString(2,newImage.getTitle());
        psmt.setLong(3,newImage.getType());
        psmt.setString(4,newImage.getFormerImagehttp());
        psmt.setString(5,oldImage.getImagehttps());
        psmt.setString(6,oldImage.getFormerImagehttp());
        psmt.execute();
    }

//    图片下载
     public void downloadImage(String urlString, String filename) throws Exception{
         URL url = new URL(urlString); // 构造URL
         URLConnection con = url.openConnection();  // 打开链接
         con.setConnectTimeout(5*1000);  //设置请求超时为5s
         InputStream is = con.getInputStream();  // 输入流
         byte[] bs = new byte[1024];  // 1K的数据缓冲
         int len;  // 读取到的数据长度
         int i = filename.length();
         for(i--;i>=0 && filename.charAt(i) != '\\' && filename.charAt(i) != '/';i--);
         String s_dir = filename.substring(0, i);
         File dir = new File(s_dir);  // 输出的文件流
         if(!dir.exists()){
             dir.mkdirs();
         }
         OutputStream os = new FileOutputStream(filename);
         // 开始读取
         while ((len = is.read(bs)) != -1) {
             os.write(bs, 0, len);
         }
         // 完毕，关闭所有链接
         os.close();
         is.close();
    }
}

